
alter  trigger tt_billbatch_ai
on dbo.tt_billbatch
for insert
as  
begin
  set nocount on
  declare @depotid integer, @productid integer,@new_quantity decimal(18,4),  @new_assqty decimal(18,4),
     @batchno varchar(200), @old_beginqty decimal(18,4), @old_beginassqty decimal(18,4),
     @produceddate datetime, @validdate datetime,
     @custom1 varchar(200), @custom2 varchar(200), @custom3 varchar(200),
     @length decimal(18,8), @width decimal(18,8), @height decimal(18,8)
     
  declare ai_cursor cursor local forward_only static optimistic for 
    select bquantity, depotid, productid, assqty, batchcode,
       productdate, validdate,
       custom1, custom2, custom3, length, width, height from inserted
  open ai_cursor
  fetch next from ai_cursor into @new_quantity, @depotid, @productid, @new_assqty, @batchno,
    @produceddate, @validdate,
    @custom1, @custom2, @custom3, @length, @width, @height
  while (@@fetch_status = 0)
  begin
    if (exists(select * from tt_bookbatch where productid = @productid and depotid = @depotid and batchcode = @batchno
      and productdate=@produceddate and validdate=@validdate
      and custom1=@custom1 and custom2=@custom2 and custom3=@custom3 and length=@length and width=@width and height=@height))
    begin
      set @old_beginqty = 0
      set @old_beginassqty = 0
      
      select @old_beginqty = beginqty, @old_beginassqty = beginassqty from tt_bookbatch 
        where productid = @productid and depotid = @depotid and batchcode = @batchno  
          and productdate=@produceddate and validdate=@validdate
          and custom1=@custom1 and custom2=@custom2 and custom3=@custom3 and length=@length and width=@width and height=@height
      
      update tt_bookbatch set beginqty = @new_quantity, beginassqty = @new_assqty,
        quantity = quantity - @old_beginqty + @new_quantity,
        assqty = assqty - @old_beginassqty + @new_assqty,
        productid = @productid, depotid = @depotid, batchcode = @batchno
      where productid = @productid and depotid = @depotid and batchcode = @batchno 
        and productdate=@produceddate and validdate=@validdate
        and custom1=@custom1 and custom2=@custom2 and custom3=@custom3 and length=@length and width=@width and height=@height
    end
    else
       insert into tt_bookbatch
        (batchcode, depotid, productid, quantity, beginqty, assqty, beginassqty,
         productdate, validdate,
         custom1, custom2, custom3, length, width, height )
       values (@batchno,@depotid,@productid,@new_quantity,@new_quantity,@new_assqty,@new_assqty,
         @produceddate, @validdate,
         @custom1, @custom2, @custom3, @length, @width, @height)
    fetch next from ai_cursor into @new_quantity, @depotid, @productid, @new_assqty, @batchno,
      @produceddate, @validdate,
      @custom1, @custom2, @custom3, @length, @width, @height   
  end
  close ai_cursor
  deallocate ai_cursor 
end
